package com.hifar.sys;

import com.hifar.mybatisplus.PublicMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Locale;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author zhuWeiWei
 * @description 数据库工具类
 * @date 2025/2/10 15:08
 */
@Component
public class DbUtils {

    private final Pattern DB_PATTERN = Pattern.compile("jdbc:(?<dbType>.*)://[^/]+/(?<schemaName>[^\\?]*).*");

    @Autowired
    private DataSource dataSource;

    @Autowired
    private PublicMapper publicMapper;

    /**
     * 查询数据库类型
     *
     * @return
     */
    public String queryDbType() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String url = metaData.getURL();
            String databaseProductName = metaData.getDatabaseProductName();
            System.out.println("databaseProductName = " + databaseProductName);
//            jdbc:mysql://192.168.3.34:3306/serversuperio_29?autoConnect=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
            Matcher matcher = DB_PATTERN.matcher(url);
            if (matcher.matches()) {
                return matcher.group("dbType");
            }
            return null;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 获取模式名
     *
     * @return
     */
    public String getSchemaName() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String url = metaData.getURL();
            Matcher matcher = DB_PATTERN.matcher(url);
            if (matcher.matches()) {
                return matcher.group("schemaName");
            }
            return null;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 查询表是否存在
     *
     * @param tableName
     * @return
     */
    public boolean tableIsExist(String tableName) {
        String dbType = this.queryDbType();
        String schemaName = this.getSchemaName();
        if (Objects.equals("dm", dbType)) {
            return publicMapper.queryDmTable(schemaName, tableName) > 0;
        } else if (Objects.equals("mysql", dbType)) {
            return publicMapper.queryMysqlTable(schemaName, tableName) > 0;
        } else if (Objects.equals("kingbase8", dbType)) {
            return publicMapper.queryKingBaseTable(schemaName, tableName.toUpperCase(Locale.ROOT)) > 0;
        }
        return false;
    }

    public void createTableName(String tableName) {
        String dbType = this.queryDbType();
        if (Objects.equals("dm", dbType)) {
            createDmTableName(tableName);
        } else if (Objects.equals("mysql", dbType)) {
            createMySqlTableName(tableName);
        } else if (Objects.equals("kingbase8", dbType)) {
            createKingBaseTableName(tableName);
        }
    }

    private void createKingBaseTableName(String tableName) {
        String createTableSql = String.format("CREATE TABLE \"public\".\"%s\" ( " +
                "   \"tagid\" character varying(32 char) NOT NULL," +
                "   \"tagname\" character varying(255 char) NOT NULL," +
                "   \"tagvalue\" character varying(255 char) NOT NULL," +
                "   \"timestamp\" datetime NOT NULL," +
                "   \"tagtype\" integer NOT NULL," +
                "   \"alarmupperlimit\" character varying(255 char) NULL," +
                "   \"alarmlowerlimit\" character varying(255 char) NULL," +
                "   \"alarmstatus\" integer NULL, " +
                "   \"alarminfo\" character varying(4000 char) NULL, " +
                "   CONSTRAINT \"%s_pkey\" PRIMARY KEY (tagid) " +
                ") ", tableName, tableName);
        publicMapper.executeSql(createTableSql);
    }


    private void createMySqlTableName(String tableName) {
        String createTableSql = String.format("CREATE TABLE `%s`  (\n" +
                "  `TagId` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '主键ID',\n" +
                "  `TagName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '标签名称',\n" +
                "  `TagValue` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '标签值',\n" +
                "  `TimeStamp` datetime NOT NULL COMMENT '时间戳',\n" +
                "  `TagType` int NOT NULL COMMENT '标签类型    1：温湿度压力、2：运行状态、3：报警信息',\n" +
                "  `AlarmUpperLimit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '报警上限',\n" +
                "  `AlarmLowerLimit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '报警下限',\n" +
                "  `AlarmStatus` int NULL DEFAULT NULL COMMENT '报警状态    1:报警、2：正常、3：不采集(不判读)、4：解析错误',\n" +
                "  `AlarmInfo` varchar(4000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '报警信息',\n" +
                "  PRIMARY KEY (`TagId`) USING BTREE,\n" +
                "  INDEX `AlarmStatus`(`AlarmStatus`) USING BTREE,\n" +
                "  INDEX `TagName`(`TagName`) USING BTREE,\n" +
                "  INDEX `TagType`(`TagType`) USING BTREE,\n" +
                "  INDEX `TimeStamp`(`TimeStamp`) USING BTREE\n" +
                ") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = DYNAMIC", tableName);
        publicMapper.executeSql(createTableSql);
    }

    private void createDmTableName(String tableName) {
        String createTableSql = String.format("CREATE TABLE \"%s\"\n" +
                "(\n" +
                "\"TAGID\" VARCHAR(32) NOT NULL,\n" +
                "\"TAGNAME\" VARCHAR(255) NOT NULL,\n" +
                "\"TAGVALUE\" VARCHAR(255) NOT NULL,\n" +
                "\"TIMESTAMP\" TIMESTAMP(0) NOT NULL,\n" +
                "\"TAGTYPE\" INT NOT NULL,\n" +
                "\"ALARMUPPERLIMIT\" VARCHAR(255),\n" +
                "\"ALARMLOWERLIMIT\" VARCHAR(255),\n" +
                "\"ALARMSTATUS\" INT,\n" +
                "\"ALARMINFO\" VARCHAR(4000),\n" +
                "\"USERNAME\" VARCHAR2(100),\n" +
                "\"REMARKS\" VARCHAR2(100),\n" +
                "NOT CLUSTER PRIMARY KEY(\"TAGID\")) STORAGE(ON \"MAIN\", CLUSTERBTR) ", tableName);

        publicMapper.executeSql(createTableSql);
    }


}
